import javax.swing.*;
import java.sql.*;
import com.mysql.jdbc.Driver;
import static java.lang.System.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.io.*;

public class DBConnector implements Serializable {
    private String dbName;
    private String query;
    private Statement st, st1;
    
    //#########################################################################
    /**
     * Creates a new instance of DBConnector 
     */
    public DBConnector(String user, String password, String host) throws SQLException {
        // MySQL database connection
        String url = "jdbc:mysql://"+host+"/information_schema";
        try {
            new Driver();
            Connection con = DriverManager.getConnection(url,user,password);
            
            st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE
                    /*,ResultSet.HOLD_CURSORS_OVER_COMMIT*/);
            st1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        } catch (SQLException e){ throw e; }
    }
    //#########################################################################
    /**
     * Retrieves the available databases
     */
    public Vector<String> getDatabases() {
        query = "show databases";
        Vector<String> results = new Vector<String>();
        try {
            ResultSet rs = st.executeQuery(query);
            while (rs.next()){
                if(! rs.getString("Database").equals("mysql") && ! rs.getString("Database").equals("information_schema"))
                {
                results.add(rs.getString("Database"));
                }
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }
        return results;
    }
    //#########################################################################
    /**
     * Select working database, must be called after getDatabases()
     */
    public void setDatabase(String name) {
        dbName = name;
        query = "use " + name;
        try {
            st.executeQuery(query);
        } catch (SQLException ex){
            ex.printStackTrace();
        }
    }
    public String getDatabase(){ return dbName; }
    //#########################################################################
    /**
     * Retrieve primary key fields
     */
    public Vector<String> getPrimaryKeys(String table) {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "desc "+table;
        Vector<String> result = new Vector<String>();        
        try {
            ResultSet rs = st1.executeQuery(query);
            while (rs.next()) {
                if (rs.getString("Key").equals("PRI")) {
                    result.add(rs.getString("Field"));
                }                
            }            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;        
    }    
    //#########################################################################
    /** 
     * Retrieves table names in the selected database
     */
    public Vector<String> getTables() {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "show tables";
        Vector<String> results = new Vector<String>();
        try {
            ResultSet rs = st.executeQuery(query);
            while (rs.next()){
                results.add(rs.getString("Tables_in_" + dbName));
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }
        return results;
    }
    //#########################################################################
    /**
     * Retrieves column names for the required table
     */
    public Vector<String> getFields(String table) {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "desc " + table;
        Vector<String> results = new Vector<String>();
        try {
            ResultSet rs = st.executeQuery(query);
            while (rs.next()){
                results.add(rs.getString("Field"));
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }
        return results;
    }
    //#########################################################################
    /** 
     * Retrieve full column data for the required field 
     */
    public Vector<String> getColumn(String table, String field) {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "select "+field+" from "+table;
        Vector<String> result = new Vector<String>();
        try {
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                result.add(rs.getString(field));
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }
        return result;
    }    
    //#########################################################################
    /*
    public void updateField(String table, String field, String data, int rownum) {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "update "+table+" set "+field+"='"+data+"' where _rowid="+rownum;
        try {
            st.executeUpdate(query);
        }
        catch (SQLException ex){
            ex.printStackTrace();
        }
    }
     */
    //#########################################################################
    /**
     * Retrieves the number of rows of the table
     */
    public int getRowCount(String table) {
        if (dbName == null) {
            throw new RuntimeException("No selected database");
        }
        query = "select COUNT(*) from "+table;
        int result=0;
        try {
            ResultSet rs = st1.executeQuery(query);
            if (rs.next()) {
                result = rs.getInt("COUNT(*)");
            }
        } catch (SQLException ex){
            ex.printStackTrace();
        }
        return result;
    }
    //#########################################################################
    /**
     * MyResultSet contains a ResultSet object with adjustable number of records
     */
    class MyResultSet {
        private int start, limit;
        private String table, field, autoKey;
        private ResultSet resultSet, findSet;
        private boolean updated, autoIncrement;
        private Vector<String> notNullFields = new Vector<String>();
        private int numOfRows, nextMatch;
        
        MyResultSet(String table, String field, int limit) {
            this.limit = limit;                    
            ResultSet temp;
            this.table = table;
            this.field = field;
            query = "select "+field+" from "+table+" limit 0,"+limit;
            try {
                resultSet = st.executeQuery(query);
                resultSet.next();
                temp = st1.executeQuery("desc "+table);
                while (temp.next()) {
                    if (temp.getString("Null").equals("NO")) {
                        notNullFields.add(temp.getString("Field"));
                    }
                    if (autoIncrement==false && temp.getString("Extra").equals("auto_increment")) {
                        autoIncrement = true;
                        autoKey = temp.getString("Field");
                    }
                }
                numOfRows = getRowCount(table);
            } catch (SQLException ex){
                ex.printStackTrace();
            }
        }
        //#########################################################################
        public void setUpdated(boolean updated){ this.updated = updated; }
        //#########################################################################
        /**
         * Retrieves fields with not NULL constraint
         */
        public Vector<String> getNotNullFields() { return notNullFields; }
        public String getAutoKey() { return autoKey; }
        public int getCurrentRowNum() { 
            int result=0;
            try {
                result = resultSet.getRow();
            } catch(SQLException ex) { ex.printStackTrace(); }
            return start+result; 
        };
        //#########################################################################
        public boolean isUpdated() { return updated; }
        //#########################################################################
        /**
         * Go to next record, if we reached the end of records block then load new 
         * block of records
         */
        public boolean next() {
            boolean result=false;
            if(getCurrentRowNum() < numOfRows){              
                result = true;
                try {
                    if (resultSet.next()==false && start < numOfRows-limit) {
                        /*if (updated == true) {

                            updated = false;
                        }*/
                        start += limit;
                        //out.println("next switch start "+start);
                        query = "select "+field+" from "+table+" limit "+start+","+limit;
                        resultSet = st.executeQuery(query);
                        resultSet.next();                    
                    }                
                } catch (SQLException ex){
                    ex.printStackTrace();
                }            
            } else {
                result = false;
            }            
            return result;
        }
        //#########################################################################
        /**
         * Go to previous record, if we reached the end of records block then load new 
         * block of records
         */ 
        public void previous() {
            try {
                if (resultSet.previous()==false && start >= limit) {
                    /*if (updated == true) {
                        resultSet.updateRow();
                        updated = false;
                    }*/
                    start -= limit;
                    //out.println("prev switch start "+start);
                    query = "select "+field+" from "+table+" limit "+start+","+limit;
                    resultSet = st.executeQuery(query);
                    resultSet.last();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        //#########################################################################
        /**
         * Go to last record in the table
         */
        public void last() {
            try {
                /*if (updated == true) {
                    resultSet.updateRow();
                    updated = false;
                }*/
                //int rows = getRowCount(table);
                start = numOfRows%limit==0 ? (numOfRows/limit-1)*limit : numOfRows-numOfRows%limit;
                //out.println("last switch start "+start);
                query = "select "+field+" from "+table+" limit "+start+","+limit;
                resultSet = st.executeQuery(query);
                resultSet.last();
                //out.println("last in dbcon::"+resultSet.getString("id"));
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        //#########################################################################
        /**
         * Go to first record in the table
         */
        public void first() {
            try {
                /*if (updated == true) {
                    resultSet.updateRow();
                    updated = false;
                } */
                start = 0;
                query = "select "+field+" from "+table+" limit 0,"+limit;
                resultSet = st.executeQuery(query);
                resultSet.next();                
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        //#########################################################################
        /**
         * Update the current record in the result set, the record is also updated in the 
         * database
         */
        public void update(HashMap<String, String> values) {
            try {
                for (String key : values.keySet()) {
                    resultSet.updateObject(key, values.get(key));
                }                
                resultSet.updateRow();
            } catch (SQLException ex) {                 
                JOptionPane.showMessageDialog(null, ex.getMessage());
            }
        }
        //#########################################################################
        /**
         * Insert a record in the result set, the record is also updated in the
         * database
         */
        public void insert(HashMap<String, String> values) throws SQLException{
            try {
                resultSet.moveToInsertRow(); // moves cursor to the insert row
                for (String key : values.keySet()){
                    if (key.equals(autoKey)) {
                        resultSet.updateNull(key);
                    } else {
                        resultSet.updateObject(key, values.get(key));
                    }
                }
                resultSet.insertRow();
                //resultSet.moveToCurrentRow();
                numOfRows++;
                last();
            } catch(SQLException ex) { throw ex; }
        }
        //#########################################################################
        /**
         * Get the value of the required field
         */
        public Object getValue(String fieldName) {
            Object result=null;
            try {
                result = resultSet.getObject(fieldName);
            } catch (SQLException ex) { ex.printStackTrace(); }
            return result;
        }      
        //#########################################################################
        /**
         * Delete the current record in the result set, the record is also deleted 
         * from the database
         */
        public void delete() {
            try {
                resultSet.deleteRow();
                //resultSet.moveToCurrentRow();
            } catch(SQLException ex) { ex.printStackTrace(); }
            numOfRows--;
        }
        //#########################################################################
        /**
         * Undo current row updates
         */
        public void undo() {
            try {
                resultSet.cancelRowUpdates();
            } catch(SQLException ex) { ex.printStackTrace(); }
        }
        //#########################################################################
        /**
         * Retrieves the last inserted id for auto-increment primary key
         */
        public int getLastInsertId(){
            ResultSet temp;
            int result=0;
            try {
                temp = st.executeQuery("select LAST_INSERT_ID()");
                temp.next();
                result = temp.getInt("LAST_INSERT_ID()");
            } catch(SQLException ex) { ex.printStackTrace(); }
            return result;
        }
        //#########################################################################
        /**
         * Find a value in the tables with a match criteria (whole word, part of field,
         * start of field)
         */
        public boolean find(String column, String value, int match) {
            if (dbName == null) {
                throw new RuntimeException("No selected database");
            }         
            boolean result=false;
            switch(match) {
                case 0:
                    query = "select "+field+" from "+table+" where "+column+"="+"'"+value+"'";                
                    break;
                case 1:
                    query = "select "+field+" from "+table+" where "+column+" like "+"'%"+value+"%'";                
                    break;
                case 2:
                    query = "select "+field+" from "+table+" where "+column+" like "+"'"+value+"%'";                
                    break;
            }
            try {
                findSet = st1.executeQuery(query); 
                result = findSet.next();
                if(result == true) { 
                  nextMatch = 0;
                  while(findSet.next()) {
                      nextMatch++;
                  }
                  System.out.println("NextMatch:: "+nextMatch);
                  first();
                  for(int i=0;i<numOfRows-1;i++) {
                    //System.out.println(getValue(column));
                    if(match==0) {                        
                        if(getValue(column).toString().toLowerCase().matches(value.toLowerCase())) {
                            break;
                        } else {
                            next(); 
                        }
                    } else if(match==1) {                        
                        if(getValue(column).toString().toLowerCase().matches(".*"+value.toLowerCase()+".*")) {
                            break;
                        } else {
                            next(); 
                        }
                    } else if(match==2) {
                        if(getValue(column).toString().toLowerCase().matches(value.toLowerCase()+".*")) {
                            break;
                        } else {
                            next(); 
                        }                        
                    }                       
                  }
                }
            } catch(SQLException ex) { ex.printStackTrace(); }
            return result;
        }        
        //#########################################################################
        /**
         * Find next after a find operation
         */
        public boolean findNext(String column, String value, int match) {
            if (dbName == null) {
                throw new RuntimeException("No selected database");
            }         
            boolean result=false;                        
            if(nextMatch > 0) {
              nextMatch--;
              result = true;    
              if (match==0) {
                  while(next()){
                    //System.out.println(getValue(column));
                    if(getValue(column).toString().toLowerCase().matches(value.toLowerCase())) {
                        break;
                    }
                  }                  
              } else if (match==1) {
                  while(next()){
                    //System.out.println(getValue(column));
                    if(getValue(column).toString().toLowerCase().matches(".*"+value.toLowerCase()+".*")) {
                        break;
                    }
                  }                  
              } else if (match==2) {
                  while(next()){
                    //System.out.println(getValue(column));
                    if(getValue(column).toString().toLowerCase().matches(value.toLowerCase()+".*")) {
                        break;
                    }
                  }                  
              }
                   
            }            
            return result;
        } 
        //#####################################################################
    }  
}
